import pool from '../database';
import type { CustomerData } from '../../types/DataTypes';

export class CustomerDAO {
    // 新增客户
    static async addCustomer(customer: CustomerData): Promise<boolean> {
        const connection = await pool.getConnection();
        try {
            const [result] = await connection.execute(
                `INSERT INTO Customer (customerName, customerId) 
                 VALUES (?, ?)`,
                [customer.customerName, customer.customerId]
            );
            return (result as any).affectedRows > 0;
        } catch (error: any) {
            if (error.code === 'ER_DUP_ENTRY') {
                throw new Error(`客户ID ${customer.customerId} 已存在`);
            }
            throw error;
        } finally {
            connection.release();
        }
    }

    // 获取所有客户
    static async getAllCustomers(): Promise<CustomerData[]> {
        const connection = await pool.getConnection();
        try {
            const [rows] = await connection.execute(
                'SELECT * FROM Customer ORDER BY customerName'
            );
            return rows as CustomerData[];
        } finally {
            connection.release();
        }
    }

    // 根据客户名模糊查询客户
    static async searchCustomersByName(customerName: string): Promise<CustomerData[]> {
        const connection = await pool.getConnection();
        try {
            const [rows] = await connection.execute(
                'SELECT * FROM Customer WHERE customerName LIKE ? ORDER BY customerName',
                [`%${customerName}%`]
            );
            return rows as CustomerData[];
        } finally {
            connection.release();
        }
    }

    // 用户登陆验证
    static async verify(customerName:string ,customerId: string): Promise<boolean> {
        const connection = await pool.getConnection();
        try {
            const [rows] = await connection.execute(
                'SELECT * FROM Customer WHERE customerName = ? AND customerId = ?',
                [customerName, customerId]
            );
            return (rows as any[]).length == 1;
        } finally {
            connection.release();
        }
    }

    // 更新客户数据
    static async updateCustomer(customerData: CustomerData): Promise<boolean> {
        const connection = await pool.getConnection();
        try {
            const [result] = await connection.execute(
                `UPDATE Customer SET 
                 customerId = ?
                 WHERE customerName = ?`,
                [customerData.customerId, customerData.customerName]
            );

            return (result as any).affectedRows > 0;
        } finally {
            connection.release();
        }
    }

    // 删除客户
    static async deleteCustomer(customerId: string): Promise<boolean> {
        const connection = await pool.getConnection();
        try {
            const [result] = await connection.execute(
                'DELETE FROM Customer WHERE customerId = ?',
                [customerId]
            );
            return (result as any).affectedRows > 0;
        } finally {
            connection.release();
        }
    }

    // 检查客户是否存在
/*    static async customerExists(customerId: string): Promise<boolean> {
        const connection = await pool.getConnection();
        try {
            const [rows] = await connection.execute(
                'SELECT 1 FROM Customer WHERE customerId = ?',
                [customerId]
            );
            return (rows as any[]).length > 0;
        } finally {
            connection.release();
        }
    }*/

    // 检查客户名是否存在
    static async customerNameExists(customerName: string): Promise<boolean> {
        const connection = await pool.getConnection();
        try {
            const [rows] = await connection.execute(
                'SELECT 1 FROM Customer WHERE customerName = ?',
                [customerName]
            );
            return (rows as any[]).length > 0;
        } finally {
            connection.release();
        }
    }
}